package com.gshocklab.bulkposter.data;

import java.text.DateFormat;
import java.text.ParseException;
import java.util.LinkedList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.gshocklab.bulkposter.data.dto.PostedMessage;
import com.gshocklab.bulkposter.data.dto.SocialAccount;

/**
 * Database adapter
 * @author vitaly gashock
 */

public final class DatabaseAdapter {
	private static final String TAG = "BulkPosterDatabaseAdapter";
	
	private static final String DATABASE_NAME = "bulkposter.db";
	private static final int DATABASE_VERSION = 1;
	
	public static final String KEY_ID = "_id";
	
	// Account table related constants
	private static final String ACCOUNT_TABLE_NAME = "account";
	public static final String USERNAME_COLUMN = "username";
	public static final String PASSWORD_COLUMN = "password";
	public static final String ACCOUNT_TYPE_COLUMN = "account_type";
	public static final String ACCOUNT_TOKEN_COLUMN = "account_token";
	public static final String ACCOUNT_SECRET_TOKEN_COLUMN = "account_secret_token";
	public static final String ACCOUNT_AVATAR_URL_COLUMN = "account_avatar_url";
	
	// Recently posted message table constants
	private static final String POSTED_MESSAGE_TABLE_NAME = "posted_message";
	public static final String POST_TEXT_COLUMN = "post_text";
	public static final String ACCOUNT_ID_COLUMN = "account_id";
	public static final String POST_DATE_COLUMN = "post_date";
	
	// Account table creation SQL
	private static final String ACCOUNT_TABLE_CREATE_SQL = "CREATE TABLE " + ACCOUNT_TABLE_NAME 
		+ "(" + KEY_ID + " integer primary key autoincrement, "
		+ USERNAME_COLUMN + " text not null, "
		+ PASSWORD_COLUMN + " text, "
		+ ACCOUNT_TOKEN_COLUMN + " text, "
		+ ACCOUNT_SECRET_TOKEN_COLUMN + " text, "
		+ ACCOUNT_AVATAR_URL_COLUMN + " text, "
		+ ACCOUNT_TYPE_COLUMN + " integer);";
	
	// Posted message table creation SQL
	private static final String POSTED_MESSAGE_TABLE_CREATE_SQL = "CREATE TABLE " + POSTED_MESSAGE_TABLE_NAME
		+ "(" + KEY_ID + " integer primary key autoincrement, "
		+ POST_TEXT_COLUMN + " text not null, "
		+ ACCOUNT_ID_COLUMN + " integer not null, "
		+ POST_DATE_COLUMN + " text);";
	
	private SQLiteDatabaseHelper dbHelper;
	
	public DatabaseAdapter(Context context) {
		dbHelper = new SQLiteDatabaseHelper(context);
	}
	
	/**
	 * Register new social account in application database
	 * @param account account object
	 * @return id of inserted account
	 */
	public long insertAccount(SocialAccount account) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		
		long result = db.insert(ACCOUNT_TABLE_NAME, null, account.prepareForPersistance());
		db.close();
		
		return result;
	}
	
	/**
	 * Get list of registered social accounts
	 * @return accounts list
	 */
	public List<SocialAccount> getRegisteredAccounts() {
		final List<SocialAccount> registeredAccounts = new LinkedList<SocialAccount>();
		
		String[] columns = {KEY_ID, USERNAME_COLUMN, PASSWORD_COLUMN, ACCOUNT_TYPE_COLUMN, ACCOUNT_TOKEN_COLUMN, ACCOUNT_SECRET_TOKEN_COLUMN, ACCOUNT_AVATAR_URL_COLUMN};
		
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor query = db.query(ACCOUNT_TABLE_NAME, columns, null, null, null, null, null);
		if (query.moveToFirst()) {
			do {
				// extract raw data from resultset
				int id = query.getInt(0);
				String username = query.getString(1);
				String password = query.getString(2);
				int accountTypeId = query.getInt(3);
				String token = query.getString(4);
				String secretToken = query.getString(5);
				String avatarUrl = query.getString(6);
				
				AccountType accountType = AccountTypesProvider.accountTypes.get(accountTypeId);
				final SocialAccount account = new SocialAccount(id, username, password, accountType);
				account.accountToken = token;
				account.secretAccountToken = secretToken;
				account.avatarUrl = avatarUrl;
				registeredAccounts.add(account);
			} while (query.moveToNext());
		}
		
		db.close();
		
		return registeredAccounts;
	}
	
	/**
	 * Find registered social account by given ID
	 * @param accountId account if
	 * @return registered account or empty account object if account was't found in registered accounts table
	 */
	public SocialAccount getRegisteredAccount(int accountId) {
		String[] columns = {USERNAME_COLUMN, PASSWORD_COLUMN, ACCOUNT_TYPE_COLUMN};
		String[] cause = {String.valueOf(accountId)};
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		
		Cursor query = db.query(ACCOUNT_TABLE_NAME, columns, KEY_ID + "=?", cause, null, null, null);
		if (query.moveToFirst()) {
			String username = query.getString(0);
			String password = query.getString(1);
			int accountTypeId = query.getInt(2);
			
			AccountType accountType = AccountTypesProvider.accountTypes.get(accountTypeId);
			
			final SocialAccount account = new SocialAccount();
			account.id = accountId;
			account.username = username;
			account.password = password;
			account.type = accountType;
			
			db.close();
			
			return account;
		}
		
		db.close();
		
		return new SocialAccount();
	}
	
	/**
	 * Remove registered social account by row index
	 * @param accountRowIndex row index
	 * @return true if account removed
	 */
	public boolean deleteSocialAccount(SocialAccount account) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		boolean result = db.delete(ACCOUNT_TABLE_NAME, KEY_ID + "=" + account.id, null) > 0;
		db.close();
		return result; 
	}
	
	/**
	 * Insert new post
	 * @param post posted message object
	 * @return inserted row ID
	 */
	public long insertPostedMessage(final PostedMessage post) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		long result = db.insert(POSTED_MESSAGE_TABLE_NAME, null, post.prepareForPersistance());
		db.close();
		return result;
	}
	
	/**
	 * Read all recently posted messages
	 * @return list of recently posted messages
	 */
	public List<PostedMessage> getPostedMessages() {
		final List<PostedMessage> postedMessages = new LinkedList<PostedMessage>();
		
		String[] columns = new String[] {KEY_ID, POST_TEXT_COLUMN, ACCOUNT_ID_COLUMN, POST_DATE_COLUMN};
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		
		Cursor query = db.query(POSTED_MESSAGE_TABLE_NAME, columns, null, null, null, null, null);
		
		while (query.moveToNext()) {
			int id = query.getInt(0);
			String postedText = query.getString(1);
			int accountId = query.getInt(2);
			String postDate = query.getString(3);
			
			final SocialAccount account = getRegisteredAccount(accountId);
			final PostedMessage post = new PostedMessage();
			post.id = id;
			post.postText = postedText;
			
			DateFormat dateFormatter = DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.LONG);
			
			try {
				post.postingDate = dateFormatter.parse(postDate);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			
			post.account = account;
			
			postedMessages.add(post);
		}
		
		db.close();
		
		return postedMessages;
	}
	
	/**
	 * Calculate recently posts count for given account
	 * @param accountId account id
	 * @return posts count
	 */
	public int getPostsCountForAccount(int accountId) {
		int count = 0;
		String[] params = {String.valueOf(accountId)};
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor result = db.rawQuery("SELECT COUNT(*) FROM " + POSTED_MESSAGE_TABLE_NAME 
				+ " WHERE " + ACCOUNT_ID_COLUMN + " = ?", params);
		
		if (result.moveToFirst()) {
			count = result.getInt(0);
		}
		
		db.close();
		
		return count;
	}
	
	/**
	 * This class helps open, create, and upgrade the database file
	 */
	private static class SQLiteDatabaseHelper extends SQLiteOpenHelper {
		SQLiteDatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(ACCOUNT_TABLE_CREATE_SQL);
			db.execSQL(POSTED_MESSAGE_TABLE_CREATE_SQL);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + POSTED_MESSAGE_TABLE_NAME);
            db.execSQL("DROP TABLE IF EXISTS " + ACCOUNT_TABLE_NAME);
            onCreate(db);
		}
	}
}
